﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SmartCodeGen.Providers;
using System.Data.SqlServerCe;
using System.Data;
using System.Collections;
using System.Collections.Specialized;
using SmartCodeGen.Providers.Base;

namespace SmartCodeGen.ImplementedProviders
{
    public class SqlCeDBSchemaProvider : DBSchemaProvider
	{

        public SqlCeDBSchemaProvider()
		{
			
		}		

        #region DbSchemaProvider Members

        /// <summary>
		/// Return the Constant NAME of this SchemaProvider
		/// </summary>
		public override string GetName()
		{
            return "SqlCeDBSchemaProvider (using System.Data.SqlServerCe namespace)";
		}
		/// <summary>
		/// Describe yourself
		/// </summary>
        public override string GetDescription( )
        {
            return "MS SQL CE schema provider using System.Data.SqlServerCe namespace. Written by zhongzf@126.com EntitiesGenerator.";
        }		
		#endregion
	
		#region Database Info
		/// <summary>
		/// Returns the DB name 
		/// </summary>
		/// <param name="DatabaseSchema"></param>
		/// <returns></returns>
        public override string GetDatabaseName(DatabaseSchema database)
		{
			string databaseName = null;			

			// Open a connection
            using (SqlCeConnection conn = new SqlCeConnection(database.ConnectionString))
			{
				conn.Open();
				databaseName = conn.Database;				
				return databaseName;
				
			}
		}		

		public override ExtendedPropertyCollection GetExtendedProperties(SchemaBase schemaObject)
		{
            throw new NotImplementedException();
		}

		/// <summary>
		/// Converts the provided system type into the corresponding DbType.
		/// </summary>
		/// <param name="type">The System type to be converted.</param>
		/// <returns>The corresponding DbType.</returns>
		private DbType GetDbTypeFromSystemType(System.Type type)
		{	
			if (type == typeof(System.String))
			{
				return DbType.String; // DbType.StringFixedLength, DbType.AnsiString, DbType.AnsiStringFixedLength
			}
			else if (type == typeof(System.Boolean))
			{
				return DbType.Boolean;
			}
			else if (type == typeof(System.Byte))
			{
				return DbType.Byte;
			}
			else if (type == typeof(System.SByte))
			{
				return DbType.SByte;
			}
			else if (type == typeof(System.Int16))
			{
				return DbType.Int16;
			}
			else if (type == typeof(System.UInt16))
			{
				return DbType.UInt16;
			}
			else if (type == typeof(System.Int32))
			{
				return DbType.Int32;
			}
			else if (type == typeof(System.UInt32))
			{
				return DbType.UInt32;
			}
			else if (type == typeof(System.Int64))
			{
				return DbType.Int64;
			}
			else if (type == typeof(System.UInt64))
			{
				return DbType.UInt64;
			}
			else if (type == typeof(System.Single))
			{
				return DbType.Single;
			}
			else if (type == typeof(System.Double))
			{
				return DbType.Double;
			}
			else if (type == typeof(System.Decimal))
			{
				return DbType.Decimal;
			}
			else if (type == typeof(System.DateTime))
			{
				return DbType.DateTime; // DbType.Date, DbType.Time
			}
			else if (type == typeof(System.Byte[]))
			{
				return DbType.Binary;
			}
            else if ( type == typeof(System.Guid))
            {
                return DbType.Guid;
            }
            else if ( type == typeof(System.Double))
            {
                return DbType.Currency;
            }
            else if ( type == typeof(System.Decimal))
            {
                return DbType.VarNumeric;
            }
			else
			{
				throw new NotSupportedException(string.Format("Conversion of System type '{0}' to a corresponding DbType is unhandled.", type.ToString()));

				// Unhandled conversions:				
				//		DbType.Object:				
			}
		}

		#endregion

		#region TABLE Info

		/// <summary>
		/// Get the Array of tables...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
        public override TableSchemaCollection GetTables(DatabaseSchema database)
        {
            return GetTables(database, database.Name);
        }

		public override TableSchemaCollection GetTables(DatabaseSchema database, string databaseName)
		{
            TableSchemaCollection list = new TableSchemaCollection();
            TableSchema tableSchema;
            string cmdText = string.Format(@"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", databaseName);

            SqlCeConnection connection = new SqlCeConnection(database.ConnectionString);
            
            SqlCeCommand cmd = new SqlCeCommand(cmdText, connection);
            SqlCeDataReader dataReader = null;
            try
            {
                connection.Open();                
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (dataReader.Read())
                {
                    tableSchema = new TableSchema(database, dataReader.GetString(0), string.Empty, DateTime.Now);
                    list.Add(tableSchema);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (dataReader != null)
                    dataReader.Close();
            }            
            return list;
		}


		/// <summary>
		/// Get all the Indexes for a given Table
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		/// 
		public override IndexSchemaCollection GetTableIndexes(TableSchema table)
		{
            //throw new NotImplementedException();                        
            //IndexSchema iSchema = new IndexSchema(
            IndexSchemaCollection list = new IndexSchemaCollection();
            IndexSchema indexSchema = null;
            string commandText = string.Format(@"select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            where TABLE_NAME ='{0}'
            and TABLE_CATALOG='{1}'
            and TABLE_SCHEMA = '{2}'
            and CONSTRAINT_TYPE in('PRIMARY KEY', 'UNIQUE')", table.Name, table.Database.Name, table.Owner);

            SqlCeConnection connection = new SqlCeConnection(table.ConnectionString);
            SqlCeCommand cmd = new SqlCeCommand(commandText, connection);
            SqlCeDataReader reader = null;
            IndexKeyInfoCollection indexKeys = new IndexKeyInfoCollection();
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                
                while (reader.Read())
                {
                    string constraintName = (string)reader["CONSTRAINT_NAME"];
                    string constraintType = (string)reader["CONSTRAINT_TYPE"];
                    IndexKeyInfo indexKey = new IndexKeyInfo(constraintName, constraintType);
                    indexKeys.Add(indexKey);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

            foreach (IndexKeyInfo indexKey in indexKeys)
            {

                ArrayList array = new ArrayList();
                string commandText1 = string.Format(@"select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION 
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc 
                join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu 
                on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA 
                and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
                and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA 
                and kcu.TABLE_NAME = tc.TABLE_NAME 
                where tc.TABLE_CATALOG='{0}'
                and tc.TABLE_NAME ='{1}'
                and tc.CONSTRAINT_NAME ='{2}'
                order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION
                ", table.Database.Name, table.Name, indexKey.ConstraintName);

                SqlCeConnection connection1 = new SqlCeConnection(table.ConnectionString);
                SqlCeCommand cmd1 = new SqlCeCommand(commandText1, connection1);
                SqlCeDataReader reader1 = null;                
                try
                {
                    connection1.Open();
                    reader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
                    
                    while (reader1.Read())
                    {
                        string columnName = (string)reader1["COLUMN_NAME"];
                        array.Add(columnName);
                    }
                    
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }                
                string[] membcol = (string[])array.ToArray(typeof(string));

                indexSchema = new IndexSchema(table, indexKey.ConstraintName, indexKey.IsPrimaryKey, indexKey.IsUnique, false, membcol);
                list.Add(indexSchema); 
            }

            return list;
		}

		/// <summary>
		/// Return the Columns for a given Table.
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override ColumnSchemaCollection GetTableColumns(TableSchema table)
		{
            ColumnSchemaCollection list = new ColumnSchemaCollection();
            ColumnSchema columnSchema = null;            
            string commandText = string.Format(@"Select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME,
                TABLE_NAME                
                 from  INFORMATION_SCHEMA.COLUMNS
                 where Table_Name = '{0}'
                 order by ORDINAL_POSITION",table.Name, table.Owner);

            SqlCeConnection connection = new SqlCeConnection(table.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(commandText, connection);
            SqlCeDataReader reader = null;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    string columnName = (string)reader["COLUMN_NAME"];
                    string nativeType = reader["DATA_TYPE"].ToString();
			
                    DbType dbType = GetDbType(nativeType);
                    AdditionalColumnInfo aci = GetAdditionalColumnInfo(reader);                 

                    columnSchema = new ColumnSchema(table, columnName, dbType, nativeType, aci.Size, aci.Precision, aci.Scale, aci.AllowDbNull);
                    list.Add(columnSchema);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

            return list;
		}

		/// <summary>
		/// Return the key info for a given table...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override TableKeySchemaCollection GetTableKeys( TableSchema table)
		{            
            
            TableKeySchemaCollection list = new TableKeySchemaCollection();
            TableKeySchema tableKeySchema = null;
            string commandText = string.Format(@"select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            where TABLE_NAME ='{0}'
            and CONSTRAINT_TYPE in('FOREIGN KEY')", table.Name, table.Database.Name, table.Owner);

            /*
            SELECT   T.TABLE_NAME, T.CONSTRAINT_NAME, K.COLUMN_NAME, K.ORDINAL_POSITION
FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS T INNER JOIN
                INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME
WHERE   (T.CONSTRAINT_TYPE = 'PRIMARY KEY')
ORDER BY T.TABLE_NAME, K.ORDINAL_POSITION
            */

            SqlCeConnection connection = new SqlCeConnection(table.ConnectionString);
            SqlCeCommand cmd = new SqlCeCommand(commandText, connection);
            SqlCeDataReader reader = null;
            TableKeyInfoCollection foreignKeys = new TableKeyInfoCollection();
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    string constraintName = (string)reader["CONSTRAINT_NAME"];
                    TableKeyInfo foreignKey = new TableKeyInfo(constraintName, "", 0, table.Name);
                    foreignKeys.Add(foreignKey);                        
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

            foreach (TableKeyInfo foreignKey in foreignKeys)
            {

                ArrayList fkMemberArray = new ArrayList();
                string commandText1 = string.Format(@"select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION 
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc 
                join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu 
                on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA 
                and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
                and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA 
                and kcu.TABLE_NAME = tc.TABLE_NAME 
                where 
                tc.TABLE_NAME ='{1}'
                and tc.CONSTRAINT_NAME ='{2}'
                order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION
                ", table.Database.Name, table.Name, foreignKey.ConstraintName);

                SqlCeConnection connection1 = new SqlCeConnection(table.ConnectionString);
                SqlCeCommand cmd1 = new SqlCeCommand(commandText1, connection1);
                SqlCeDataReader reader1 = null;
                try
                {
                    connection1.Open();
                    reader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reader1.Read())
                    {
                        string columnName = (string)reader1["COLUMN_NAME"];
                        fkMemberArray.Add(columnName);
                    }

                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
                string[] fkmembcol = (string[])fkMemberArray.ToArray(typeof(string));

                //PrimaryKey
                string commandText2 = string.Format(@"select t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.ORDINAL_POSITION, t1.TABLE_NAME
                from Information_Schema.Key_Column_Usage t1, Information_Schema.Referential_constraints t2                
                where t2.Constraint_name = '{0}'
                and t2.Unique_Constraint_Name = t1.Constraint_Name
                and table_Catalog = '{1}'
                order by Ordinal_Position", foreignKey.ConstraintName, table.Database.Name);

                TableKeyInfoCollection primaryKeys = new TableKeyInfoCollection();
                SqlCeConnection connection2 = new SqlCeConnection(table.ConnectionString);

                SqlCeCommand cmd2 = new SqlCeCommand(commandText2, connection2);
                SqlCeDataReader reader2 = null;
                try
                {
                    connection2.Open();
                    reader2 = cmd2.ExecuteReader(CommandBehavior.CloseConnection);
                    while (reader2.Read())
                    {
                        string constraintName = (string)reader2["CONSTRAINT_NAME"];
                        string columnName = reader2["COLUMN_NAME"].ToString();
                        int ordinalPosition = (int)reader2["ORDINAL_POSITION"];
                        string tableName = reader2["TABLE_NAME"].ToString();
                        TableKeyInfo primaryKey = new TableKeyInfo(constraintName, columnName, ordinalPosition, tableName);
                        primaryKeys.Add(primaryKey);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (reader2 != null)
                        reader2.Close();
                }

                string[] pkmembcol = new string[primaryKeys.Count];
                int j = 0;
                string pkTableName = string.Empty;
                foreach (TableKeyInfo pk in primaryKeys)
                {
                    if (pkTableName == string.Empty)
                       pkTableName = pk.TableName;
                    pkmembcol[j] = pk.ColumnName;
                    j = j + 1;
                }

                tableKeySchema = new TableKeySchema(table.Database,foreignKey.ConstraintName, fkmembcol, foreignKey.TableName, pkmembcol,pkTableName );
                list.Add(tableKeySchema);
            }

            return list;


		}

		
		/// <summary>
		/// Return the PK for a given Table...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override PrimaryKeySchema GetTablePrimaryKey(TableSchema table)
		{  
            PrimaryKeySchema primaryKeySchema = null;
            string commandText = string.Format(@"select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            where TABLE_NAME ='{0}'
            and CONSTRAINT_TYPE in('PRIMARY KEY')", table.Name, table.Database.Name, table.Owner);

            SqlCeConnection connection = new SqlCeConnection(table.ConnectionString);
            SqlCeCommand cmd = new SqlCeCommand(commandText, connection);
            SqlCeDataReader reader = null;
            TableKeyInfoCollection primaryKeys = new TableKeyInfoCollection();
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read())
                {
                    string constraintName = (string)reader["CONSTRAINT_NAME"];
                    //string constraintType = (string)reader["CONSTRAINT_TYPE"];
                    TableKeyInfo primaryKey = new TableKeyInfo(constraintName, "",0,table.Name);
                    primaryKeys.Add(primaryKey);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

            foreach (TableKeyInfo primaryKey in primaryKeys)
            {

                ArrayList array = new ArrayList();
                /*
                string commandText1 = string.Format(@"select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION 
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc 
                join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu 
                on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA 
                and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
                and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA 
                and kcu.TABLE_NAME = tc.TABLE_NAME 
                where
                tc.TABLE_NAME ='{1}'
                and tc.CONSTRAINT_NAME ='{2}'
                order by kcu.TABLE_SCHEMA, kcu.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION
                ", table.Database.Name, table.Name, primaryKey.ConstraintName);
                 */
                string commandText1 = string.Format(@"SELECT  
    T.TABLE_NAME,  
    T.CONSTRAINT_NAME,  
    K.COLUMN_NAME,  
    K.ORDINAL_POSITION  
FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
    ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME  
WHERE 
    T.CONSTRAINT_TYPE = 'PRIMARY KEY'  
     AND
    T.TABLE_NAME ='{1}'
     AND
     T.CONSTRAINT_NAME ='{2}'
ORDER BY 
    T.TABLE_NAME, 
    K.ORDINAL_POSITION",
                       table.Database.Name, table.Name, primaryKey.ConstraintName);

                /*
                 * 
SELECT  
    T.TABLE_NAME,  
    T.CONSTRAINT_NAME,  
    K.COLUMN_NAME,  
    K.ORDINAL_POSITION  
FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
    INNER JOIN 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE K 
    ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME  
WHERE 
    T.CONSTRAINT_TYPE = 'PRIMARY KEY'  
     AND
    T.TABLE_NAME ='{1}'
     AND
     T.CONSTRAINT_NAME ='{2}'
ORDER BY 
    T.TABLE_NAME, 
    K.ORDINAL_POSITION
                 * */

                SqlCeConnection connection1 = new SqlCeConnection(table.ConnectionString);
                SqlCeCommand cmd1 = new SqlCeCommand(commandText1, connection1);
                SqlCeDataReader reader1 = null;
                try
                {
                    connection1.Open();
                    reader1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reader1.Read())
                    {
                        string columnName = (string)reader1["COLUMN_NAME"];
                        array.Add(columnName);
                    }

                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                }
                string[] membcol = (string[])array.ToArray(typeof(string));

                primaryKeySchema = new PrimaryKeySchema(table, primaryKey.ConstraintName, membcol);
                
            }

            return primaryKeySchema;
		}


		/// <summary>
		/// Return the data from a table in a System.DataTable object...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="table"></param>
		/// <returns></returns>
		public override DataTable GetTableData(TableSchema table)
		{
            //throw new NotImplementedException();
            string commandText = string.Format("SELECT * FROM {0}", table.Name);
            using (SqlCeConnection connection =   new SqlCeConnection(table.ConnectionString))
            {
                try
                {
                    SqlCeDataAdapter adapter = new SqlCeDataAdapter();
                    DataSet dataset = new DataSet();
                    adapter.SelectCommand = new SqlCeCommand(
                        commandText, connection);
                    adapter.Fill(dataset);
                    return dataset.Tables[0];
                }
                catch
                {
                    throw;
                }                
            }

            
		}
		#endregion

		#region VIEW Info

		/// <summary>
		/// Return array of Views
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
		public override ViewSchemaCollection GetViews(DatabaseSchema database)
		{
            //throw new NotImplementedException();

            ViewSchemaCollection list = new ViewSchemaCollection();
            ViewSchema viewSchema;
            string cmdText = string.Format(@"SELECT TABLE_NAME, TABLE_SCHEMA OWNER FROM INFORMATION_SCHEMA.VIEWS", database.Name);

            SqlCeConnection connection = new SqlCeConnection(database.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(cmdText, connection);
            SqlCeDataReader reader = null;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    viewSchema = new ViewSchema(database, reader.GetString(0), reader.GetString(1), reader.GetDateTime(2));
                    list.Add(viewSchema);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
		}


		/// <summary>
		/// Returns the Text of a View's definition
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override string GetViewText(ViewSchema view)
		{
                        
            //throw new NotImplementedException();
            
            

            string cmdText = string.Format(@"select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS
            where Table_Name = '{0}'
            and Table_catalog ='{1}' 
            and Table_Schema ='{2}'", view.Name, view.Database.Name, view.Owner);

            SqlCeConnection connection = new SqlCeConnection(view.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(cmdText, connection);
            SqlCeDataReader reader = null;
            string viewText = string.Empty;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    viewText = reader.GetString(0);
                    
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return viewText;
		}

		/// <summary>
		/// Return all the columns for a view...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override ViewColumnSchemaCollection GetViewColumns(ViewSchema view)
		{
            //throw new NotImplementedException();

            ViewColumnSchemaCollection list = new ViewColumnSchemaCollection();
            ViewColumnSchema columnSchema = null;
            //                                    0             1           2               3                         4                5                 6                  7            8             9   
            string commandText = string.Format(@"Select COLUMN_NAME, DATA_TYPE, IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, COLLATION_NAME, TABLE_NAME, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
                 from  INFORMATION_SCHEMA.COLUMNS
                 where Table_Name = '{0}'
                 order by ORDINAL_POSITION", view.Name, view.Owner);

            SqlCeConnection connection = new SqlCeConnection(view.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(commandText, connection);
            SqlCeDataReader reader = null;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    string columnName = (string)reader["COLUMN_NAME"];
                    string nativeType = reader["DATA_TYPE"].ToString();

                    DbType dbType = GetDbType(nativeType);
                    AdditionalColumnInfo aci = GetAdditionalColumnInfo(reader);

                    columnSchema = new ViewColumnSchema(view, columnName, dbType, nativeType, aci.Size, aci.Precision, aci.Scale, aci.AllowDbNull);
                    list.Add(columnSchema);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }

            return list;
		}


		/// <summary>
		/// Return all the Rows from a view
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="view"></param>
		/// <returns></returns>
		public override DataTable GetViewData(ViewSchema view)
		{
            //throw new NotImplementedException();
            string commandText = string.Format("SELECT * FROM {0}", view.Name);
            DataSet dataset = null;
            using (SqlCeConnection connection = new SqlCeConnection(view.ConnectionString))
            {
                try
                {
                    SqlCeDataAdapter adapter = new SqlCeDataAdapter();
                    dataset = new DataSet();
                    adapter.SelectCommand = new SqlCeCommand(
                        commandText, connection);
                    adapter.Fill(dataset);
                    
                }
                catch
                {
                    throw;
                }
            }
            return dataset.Tables[0];
		}

		#endregion

		#region COMMAND Info

		/// <summary>
		/// Return array of commands...
		/// </summary>
		/// <param name="connectionString"></param>
		/// <param name="database"></param>
		/// <returns></returns>
		public override CommandSchemaCollection GetCommands(DatabaseSchema database)
		{
            //throw new NotImplementedException();
            CommandSchemaCollection list = new CommandSchemaCollection();
            CommandSchema commandSchema;
            string cmdText = string.Format(@"select ROUTINE_NAME, ROUTINE_SCHEMA,CREATED
            from INFORMATION_SCHEMA.ROUTINES
            where ROUTINE_CATALOG = '{0}'               
            ORDER BY 1", database.Name);

            SqlCeConnection connection = new SqlCeConnection(database.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(cmdText, connection);
            SqlCeDataReader reader = null;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    commandSchema = new CommandSchema(database, reader.GetString(0), reader.GetString(1), reader.GetDateTime(2));
                    list.Add(commandSchema);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return list;
		}

		public override ParameterSchemaCollection GetCommandParameters(CommandSchema command)
		{
            throw new NotImplementedException();
            
		}

		public override string GetCommandText(CommandSchema command)
		{
			// TODO: Return command text of simple procedures?
			//throw new NotImplementedException("Retrieval of command text has not yet been implemented.");
            
            string cmdText = string.Format(@"select ROUTINE_DEFINITION
            from INFORMATION_SCHEMA.ROUTINES
            where ROUTINE_CATALOG = '{0}'
            and ROUTINE_NAME='{1}'
            ORDER BY 1", command.Database.Name, command.Name);

            SqlCeConnection connection = new SqlCeConnection(command.ConnectionString);

            SqlCeCommand cmd = new SqlCeCommand(cmdText, connection);
            SqlCeDataReader reader = null;
            string routineDefinition = string.Empty;
            try
            {
                connection.Open();
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    routineDefinition = reader.GetString(0);                    
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
            return routineDefinition;
		}

		public override CommandResultSchemaCollection GetCommandResultSchemas(CommandSchema command)
		{
			throw new NotImplementedException();
			
		}

		#endregion

		#region Helpers
        //ref http://lists.ximian.com/pipermail/mono-patches/2002-November/010247.html
        private Hashtable sqlTypes = null;
        private Hashtable _SqlTypes
        {
            get 
            {
                if (sqlTypes == null)
                {
                    sqlTypes = new Hashtable(30);
                    sqlTypes.Add("bigint", DbType.Int64);                    
                    sqlTypes.Add("bit", DbType.Boolean);                    
                    sqlTypes.Add("binary", DbType.Binary);
                    sqlTypes.Add("char", DbType.AnsiStringFixedLength);
                    sqlTypes.Add("datetime", DbType.DateTime);
                    sqlTypes.Add("decimal", DbType.Decimal);
                    sqlTypes.Add("float", DbType.Double);
                    sqlTypes.Add("int", DbType.Int32);
                    sqlTypes.Add("int identity", DbType.Int32);
                    sqlTypes.Add("image", DbType.Byte);                    
                    sqlTypes.Add("money", DbType.Currency);
                    sqlTypes.Add("nvarchar", DbType.String);
                    sqlTypes.Add("nvarchar(max)", DbType.String);
                    sqlTypes.Add("numeric", DbType.Decimal);
                    sqlTypes.Add("ntext", DbType.String);
                    sqlTypes.Add("nchar", DbType.StringFixedLength);
                    sqlTypes.Add("real", DbType.Single);
                    sqlTypes.Add("smalldatetime", DbType.Time);
                    sqlTypes.Add("smallint", DbType.Int16);
                    sqlTypes.Add("sysname", DbType.String);
                    sqlTypes.Add("smallmoney", DbType.Currency);
                    sqlTypes.Add("sql_variant", DbType.Object);
                    sqlTypes.Add("text", DbType.String);
                    sqlTypes.Add("timestamp", DbType.Time);  
                    sqlTypes.Add("tinyint", DbType.Binary);
                    sqlTypes.Add("uniqueidentifier", DbType.Guid);
                    sqlTypes.Add("varchar", DbType.AnsiString);
                    sqlTypes.Add("varchar(max)", DbType.String);
                    sqlTypes.Add("varbinary", DbType.Binary);
                    sqlTypes.Add("varbinary(max)", DbType.Byte);                    
                    sqlTypes.Add("xml",DbType.Xml);                    
                }
                return sqlTypes;
            }
        }

		private System.Data.DbType GetDbType(string nativeType)
		{
            return (DbType)_SqlTypes[nativeType.ToLower()];
		}

        private AdditionalColumnInfo GetAdditionalColumnInfo( SqlCeDataReader reader)
        {
            AdditionalColumnInfo columnInfo = new AdditionalColumnInfo(0, (byte)0, 0, false);
            string nativeType = reader["DATA_TYPE"].ToString();
            if ((nativeType == "varchar")
                || (nativeType == "char")
                || (nativeType == "nvarchar")
                || (nativeType == "varbinary"))
            {
                columnInfo.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"];
            }
            if ((nativeType == "bigint")
                || (nativeType == "decimal")
                //|| (nativeType == "datetime")
                || (nativeType == "int")
                || (nativeType == "money")
                || (nativeType == "numeric")
                || (nativeType == "smallint")
                //|| (nativeType == "smalldatetime")
                || (nativeType == "smallmoney")
                || (nativeType == "tinyint")
                || (nativeType == "numeric"))
            {
                columnInfo.Precision = reader.IsDBNull(reader.GetOrdinal("NUMERIC_PRECISION")) ? (byte)0 : Convert.ToByte(reader["NUMERIC_PRECISION"]);
                columnInfo.Scale = reader.IsDBNull(reader.GetOrdinal("NUMERIC_SCALE")) ? 0 : (int)Convert.ToByte(reader["NUMERIC_SCALE"]);
            }

            if ((nativeType == "float")
                || (nativeType == "real"))
            {
                columnInfo.Precision = reader.IsDBNull(reader.GetOrdinal("NUMERIC_PRECISION")) ? (byte)0 : Convert.ToByte(reader["NUMERIC_PRECISION"]);
            }

           
            if (reader["IS_NULLABLE"] != DBNull.Value)
            {
                string s = (string)reader["IS_NULLABLE"];
                columnInfo.AllowDbNull = ("yes" == s.ToLower());
            }

            return columnInfo;
        }
		
		
		

		#endregion

        #region Provider specific behaviors
        public override void Initialize(string name, NameValueCollection configValue)
        {

        }

        public override string Name
        {
            get
            {
                return "SqlCeDBSchemaProvider";
            }
        }
        #endregion

    }

    class IndexKeyInfo : BaseObject
    {
        public IndexKeyInfo(string constraintName, string constraintType)
        {
            this.constraintName = constraintName;
            this.constraintType = constraintType;
        }
        private string constraintName;

        public string ConstraintName
        {
            get { return constraintName; }
            set { constraintName = value; }
        }

        private string constraintType;

        public string ConstraintType
        {
            get { return constraintType; }
            set { constraintType = value; }
        }

        public bool IsPrimaryKey
        {
            get
            {
                if (constraintType == "PRIMARY KEY")
                    return true;
                else return false;
            }
        }
        public bool IsUnique
        {
            get
            {
                if (constraintType == "UNIQUE")
                    return true;
                else return false;
            }
        }
    }


    class TableKeyInfo : BaseObject
    {
        public TableKeyInfo(string constraintName, string columnName, int ordinalPosition, string tableName)
        {
            this.constraintName = constraintName;
            this.columnName = columnName;
            this.ordinalPosition = ordinalPosition;
            this.tableName = tableName;
        }

        private string constraintName;

        public string ConstraintName
        {
            get { return constraintName; }
            set { constraintName = value; }
        }
        private string columnName;

        public string ColumnName
        {
            get { return columnName; }
            set { columnName = value; }
        }

        private int ordinalPosition;

        public int OrdinalPosition
        {
            get { return ordinalPosition; }
            set { ordinalPosition = value; }
        }

        private string tableName;

        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }


    }

    class TableKeyInfoCollection : BaseCollection
    {
        public TableKeyInfoCollection() : base() { }

        public void Add(TableKeyInfo obj)
        {
            Add(0, obj);
        }
    }

    class IndexKeyInfoCollection : BaseCollection
    {
        public IndexKeyInfoCollection() : base() { }

        public void Add(IndexKeyInfo obj)
        {
            Add(0, obj);
        }
    }

    class AdditionalColumnInfo
    {
        public AdditionalColumnInfo(int size, byte precision, int scale, bool allowDbNull)
        {
            this.size = size;
            this.precision = precision;
            this.scale = scale;
            this.allowDbNull = allowDbNull;
        }
        private int size;

        public int Size
        {
            get { return size; }
            set { size = value; }
        }
        private byte precision;

        public byte Precision
        {
            get { return precision; }
            set { precision = value; }
        }
        private int scale;

        public int Scale
        {
            get { return scale; }
            set { scale = value; }
        }

        private bool allowDbNull;

        public bool AllowDbNull
        {
            get { return allowDbNull; }
            set { allowDbNull = value; }
        }

    }

}


